import logging
import requests
import time
from typing import Dict, List, Optional, Any
from urllib.parse import urlencode
from app.utils.mysql_helper import MySQLHelper
from app.utils.error_handling import BadRequestError, InternalServerError
from app.conf.config import logger

class ShopifyProductImportService:
    """
    Shopify商品数据导入服务
    用于从Shopify API获取商品和变体数据并写入数据库
    """
    
    def __init__(self):
        """初始化服务"""
        self.db_helper = MySQLHelper(auto_commit=False)  # 禁用自动提交，使用手动事务管理
        self.api_version = "2025-10"
        
    def get_user_access_token(self, shopify_domain: str) -> str:
        """
        从数据库获取用户的access_token
        
        Args:
            shopify_domain: 店铺域名
            
        Returns:
            str: access_token
            
        Raises:
            BadRequestError: 当店铺不存在时
        """
        try:
            # 依次从可能存在的表中查询，确保兼容历史代码与表结构
            query_order = [
                "SELECT access_token FROM shopify_users WHERE shop_domain = %s LIMIT 1",
                "SELECT access_token FROM shopify_app.users WHERE shop_domain = %s LIMIT 1",
                "SELECT access_token FROM users WHERE shop_domain = %s LIMIT 1"
            ]
            access_token = None
            
            for query in query_order:
                try:
                    result = self.db_helper.execute_query(query=query, params=(shopify_domain,))
                    if result and result[0].get('access_token'):
                        access_token = result[0]['access_token']
                        break
                except Exception:
                    # 如果查询失败，尝试下一个表
                    continue
            
            if not access_token:
                raise BadRequestError(f"Shop {shopify_domain} not found")
            
            return access_token
            
        except Exception as e:
            if isinstance(e, BadRequestError):
                raise
            logger.error(f"Failed to get access token: {str(e)}")
            raise InternalServerError(f"Failed to get access token")
    
    def _build_shopify_url(self, shop_domain: str, endpoint: str, params: Optional[Dict] = None) -> str:
        """
        构建Shopify API的完整URL
        
        Args:
            shop_domain: Shopify店铺域名
            endpoint: API端点
            params: URL参数
            
        Returns:
            str: 完整的Shopify API URL
        """
        try:
            # 确保shop_domain格式正确
            if shop_domain.startswith(('http://', 'https://')):
                shop_domain = shop_domain.split('://', 1)[1]
            if shop_domain.endswith('/'):
                shop_domain = shop_domain[:-1]
            
            # 如果域名不包含myshopify.com，自动添加
            if not shop_domain.endswith('.myshopify.com'):
                shop_domain = f"{shop_domain}.myshopify.com"
            
            # 构建基础URL
            base_url = f"https://{shop_domain}/admin/api/{self.api_version}{endpoint}"
            
            # 添加参数（确保正确编码）
            if params:
                filtered_params = {k: v for k, v in params.items() if v is not None}
                query_string = urlencode(filtered_params, doseq=True, safe=',')
                url = f"{base_url}?{query_string}" if query_string else base_url
            else:
                url = base_url
            
            logger.debug(f"构建Shopify API URL: {url}")
            return url
            
        except Exception as e:
            logger.error(f"构建Shopify URL异常: {str(e)}")
            raise
    
    def _get_headers(self, access_token: str) -> Dict[str, str]:
        """
        获取Shopify API请求所需的标准头信息
        
        Args:
            access_token: Shopify访问令牌
            
        Returns:
            Dict: 请求头信息
        """
        return {
            'Content-Type': 'application/json',
            'X-Shopify-Access-Token': access_token,
            'Accept': 'application/json'
        }
    
    def _fetch_products_with_pagination(self, shopify_domain: str, access_token: str, fields: str) -> List[Dict]:
        """
        获取店铺商品数据（仅请求一次，不分页）
        
        Args:
            shopify_domain: 店铺域名
            access_token: 访问令牌
            fields: 要获取的字段
            
        Returns:
            List[Dict]: 商品数据列表
        """
        all_products = []
        
        logger.info(f"开始获取商品数据，店铺: {shopify_domain}")
        
        try:
            # 构建URL参数 - 只包含fields，不包含分页参数
            params = {
                'fields': fields
            }
            
            # 构建URL
            url = self._build_shopify_url(shopify_domain, '/products.json', params)
            headers = self._get_headers(access_token)
            
            logger.info(f"请求商品数据，URL: {url}")
            
            # 发送请求
            response = requests.get(url, headers=headers, timeout=30)
            
            # 检查响应状态
            if response.status_code == 429:
                # 速率限制，等待后重试
                retry_after = int(response.headers.get('Retry-After', 2))
                logger.warning(f"触发速率限制，等待 {retry_after} 秒后重试")
                time.sleep(retry_after)
                # 重试一次
                response = requests.get(url, headers=headers, timeout=30)
            
            if response.status_code != 200:
                logger.error(f"获取商品数据失败，状态码: {response.status_code}, 响应: {response.text}")
                return all_products
            
            # 解析响应数据
            data = response.json()
            products = data.get('products', [])
            
            if not products:
                logger.info(f"没有获取到商品数据")
                return all_products
            
            all_products.extend(products)
            logger.info(f"获取到 {len(products)} 个商品")
            
        except requests.exceptions.RequestException as e:
            logger.error(f"请求异常: {str(e)}")
        except Exception as e:
            logger.error(f"获取商品数据异常: {str(e)}")
        
        logger.info(f"获取完成，共获取 {len(all_products)} 个商品")
        return all_products
    
    def import_products(self, shopify_domain: str) -> Dict[str, Any]:
        """
        导入商品数据到数据库
        
        Args:
            shopify_domain: 店铺域名
            
        Returns:
            Dict: 导入结果
        """
        logger.info(f"开始导入商品数据，店铺: {shopify_domain}")
        
        try:
            # 1. 获取access_token
            access_token = self.get_user_access_token(shopify_domain)
            
            # 2. 定义要获取的字段
            fields = "id,title,body_html,vendor,product_type,created_at,published_at,template_suffix,published_scope,tags,status,variants"
            
            # 3. 分页获取所有商品数据
            products = self._fetch_products_with_pagination(shopify_domain, access_token, fields)
            
            if not products:
                logger.warning(f"未获取到任何商品数据，店铺: {shopify_domain}")
                return {
                    "is_success": 0,
                    "message": "No products found",
                    "total": 0,
                    "inserted": 0,
                    "updated": 0
                }
            
            # 4. 写入数据库
            inserted_count = 0
            updated_count = 0
            
            # 使用事务确保数据完整性
            with self.db_helper.transaction():
                for product in products:
                    try:
                        # 准备数据
                        product_data = {
                            'product_id': product.get('id'),
                            'shop_domain': shopify_domain,
                            'title': product.get('title', ''),
                            'body_html': product.get('body_html'),
                            'vendor': product.get('vendor'),
                            'product_type': product.get('product_type'),
                            'status': product.get('status', 'active'),
                            'tags': ','.join(product.get('tags', [])) if isinstance(product.get('tags'), list) else product.get('tags'),
                            'created_at': product.get('created_at'),
                            'published_at': product.get('published_at'),
                            'template_suffix': product.get('template_suffix'),
                            'published_scope': product.get('published_scope')
                        }
                        
                        # 检查是否已存在
                        check_sql = "SELECT id FROM shopify_products WHERE product_id = %s AND shop_domain = %s"
                        existing = self.db_helper.execute_query(query=check_sql, params=(product_data['product_id'], shopify_domain))
                        
                        if existing:
                            # 更新
                            update_sql = """
                                UPDATE shopify_products 
                                SET title = %s, body_html = %s, vendor = %s, product_type = %s, 
                                    status = %s, tags = %s, created_at = %s, published_at = %s, 
                                    template_suffix = %s, published_scope = %s
                                WHERE product_id = %s AND shop_domain = %s
                            """
                            self.db_helper.execute_update(
                                query=update_sql,
                                params=(
                                    product_data['title'],
                                    product_data['body_html'],
                                    product_data['vendor'],
                                    product_data['product_type'],
                                    product_data['status'],
                                    product_data['tags'],
                                    product_data['created_at'],
                                    product_data['published_at'],
                                    product_data['template_suffix'],
                                    product_data['published_scope'],
                                    product_data['product_id'],
                                    shopify_domain
                                )
                            )
                            updated_count += 1
                        else:
                            # 插入
                            insert_sql = """
                                INSERT INTO shopify_products 
                                (product_id, shop_domain, title, body_html, vendor, product_type, 
                                 status, tags, created_at, published_at, template_suffix, published_scope)
                                VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                            """
                            self.db_helper.execute_update(
                                query=insert_sql,
                                params=(
                                    product_data['product_id'],
                                    product_data['shop_domain'],
                                    product_data['title'],
                                    product_data['body_html'],
                                    product_data['vendor'],
                                    product_data['product_type'],
                                    product_data['status'],
                                    product_data['tags'],
                                    product_data['created_at'],
                                    product_data['published_at'],
                                    product_data['template_suffix'],
                                    product_data['published_scope']
                                )
                            )
                            inserted_count += 1
                            
                    except Exception as e:
                        logger.error(f"写入商品数据失败，product_id: {product.get('id')}, 错误: {str(e)}")
                        continue
            
            logger.info(f"商品数据导入完成，店铺: {shopify_domain}, 总数: {len(products)}, 新增: {inserted_count}, 更新: {updated_count}")
            
            return {
                "is_success": 1,
                "message": "Products imported successfully",
                "total": len(products),
                "inserted": inserted_count,
                "updated": updated_count
            }
            
        except BadRequestError as e:
            logger.error(f"导入商品数据失败: {str(e)}")
            return {
                "is_success": 0,
                "error": str(e)
            }
        except Exception as e:
            logger.error(f"导入商品数据异常: {str(e)}", exc_info=True)
            return {
                "is_success": 0,
                "error": f"Internal error: {str(e)}"
            }
    
    def import_product_variants(self, shopify_domain: str) -> Dict[str, Any]:
        """
        导入商品变体数据到数据库
        
        Args:
            shopify_domain: 店铺域名
            
        Returns:
            Dict: 导入结果
        """
        try:
            # 获取access_token
            access_token = self.get_user_access_token(shopify_domain)
            
            # 构建字段列表，确保包含variants关键字段
            # 使用variants字段获取变体数据
            fields = 'variants'
            
            # 获取商品数据
            products = self._fetch_products_with_pagination(shopify_domain, access_token, fields)
            
            # 调试日志：打印商品数量和第一个商品的变体结构
            logger.info(f"获取到 {len(products)} 个商品")
            if products:
                logger.debug(f"第一个商品的变体数量: {len(products[0].get('variants', []))}")
                if products[0].get('variants'):
                    logger.debug(f"第一个变体的结构: {products[0]['variants'][0].keys()}")
            
            # 使用事务处理所有变体数据的写入操作
            with self.db_helper.transaction():
                # 处理变体数据
                inserted_count = 0
                updated_count = 0
                total_variants = 0
                
                for product in products:
                    variants = product.get('variants', [])
                    total_variants += len(variants)
                    
                    for variant in variants:
                        try:
                            # 准备数据
                            variant_data = {
                                'id': variant.get('id'),
                                'product_id': variant.get('product_id'),
                                # 'shop_domain': shopify_domain,
                                'title': variant.get('title', ''),
                                'price': variant.get('price'),
                                'compare_at_price': variant.get('compare_at_price'),
                                'sku': variant.get('sku'),
                                'position': variant.get('position', 1),
                                'inventory_policy': variant.get('inventory_policy', 'deny'),
                                'inventory_management': variant.get('inventory_management'),
                                'inventory_quantity': variant.get('inventory_quantity', 0),
                                'fulfillment_service': variant.get('fulfillment_service', 'manual'),
                                'weight': variant.get('weight'),
                                'weight_unit': variant.get('weight_unit', 'kg'),
                                'option1': variant.get('option1'),
                                'option2': variant.get('option2'),
                                'option3': variant.get('option3'),
                                'taxable': variant.get('taxable', True),
                                'barcode': variant.get('barcode'),
                                'image_id': variant.get('image_id'),
                                'created_at': variant.get('created_at'),
                                'updated_at': variant.get('updated_at')
                            }
                            
                            # 检查是否已存在
                            check_sql = "SELECT id FROM shopify_product_variants WHERE id = %s AND product_id = %s"
                            existing = self.db_helper.execute_query(
                                query=check_sql,
                                params=(variant_data['id'], variant_data.get('product_id'))
                            )
                            
                            if existing:
                                # 更新
                                update_sql = """
                                    UPDATE shopify_product_variants 
                                    SET title = %s, price = %s, compare_at_price = %s, sku = %s, 
                                        position = %s, inventory_policy = %s, inventory_management = %s, 
                                        inventory_quantity = %s, fulfillment_service = %s, weight = %s, 
                                        weight_unit = %s, option1 = %s, option2 = %s, option3 = %s, 
                                        taxable = %s, barcode = %s, image_id = %s, created_at = %s, 
                                        updated_at = %s
                                    WHERE id = %s AND product_id = %s
                                """
                                self.db_helper.execute_update(
                                    query=update_sql,
                                    params=(
                                        variant_data['title'],
                                        variant_data['price'],
                                        variant_data['compare_at_price'],
                                        variant_data['sku'],
                                        variant_data['position'],
                                        variant_data['inventory_policy'],
                                        variant_data['inventory_management'],
                                        variant_data['inventory_quantity'],
                                        variant_data['fulfillment_service'],
                                        variant_data['weight'],
                                        variant_data['weight_unit'],
                                        variant_data['option1'],
                                        variant_data['option2'],
                                        variant_data['option3'],
                                        variant_data['taxable'],
                                        variant_data['barcode'],
                                        variant_data['image_id'],
                                        variant_data['created_at'],
                                        variant_data['updated_at'],
                                        variant_data['id'],
                                        variant_data.get('product_id')
                                    )
                                )
                                updated_count += 1
                            else:
                                # 插入
                                insert_sql = """
                                    INSERT INTO shopify_product_variants 
                                    (id, product_id, title, price, compare_at_price, 
                                     sku, position, inventory_policy, inventory_management, inventory_quantity, 
                                     fulfillment_service, weight, weight_unit, option1, option2, option3, 
                                     taxable, barcode, image_id, created_at, updated_at)
                                    VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                                """
                                self.db_helper.execute_update(
                                    query=insert_sql,
                                    params=(
                                        variant_data['id'],
                                        variant_data.get('product_id'),
                                        # variant_data['shop_domain'],
                                        variant_data['title'],
                                        variant_data['price'],
                                        variant_data['compare_at_price'],
                                        variant_data['sku'],
                                        variant_data['position'],
                                        variant_data['inventory_policy'],
                                        variant_data['inventory_management'],
                                        variant_data['inventory_quantity'],
                                        variant_data['fulfillment_service'],
                                        variant_data['weight'],
                                        variant_data['weight_unit'],
                                        variant_data['option1'],
                                        variant_data['option2'],
                                        variant_data['option3'],
                                        variant_data['taxable'],
                                        variant_data['barcode'],
                                        variant_data['image_id'],
                                        variant_data['created_at'],
                                        variant_data['updated_at']
                                    )
                                )
                                inserted_count += 1
                                
                        except Exception as e:
                            logger.error(f"写入变体数据失败，id: {variant.get('id')}, 错误: {str(e)}")
                            continue
            
            # 返回结果，即使没有找到变体也返回成功
            return {
                "is_success": 1,
                "message": "Product variants imported successfully",
                "total": total_variants,
                "inserted": inserted_count,
                "updated": updated_count
            }
            
        except BadRequestError as e:
            logger.error(f"导入商品变体数据失败: {str(e)}")
            return {
                "is_success": 0,
                "error": str(e)
            }
        except Exception as e:
            logger.error(f"导入商品变体数据异常: {str(e)}")
            return {
                "is_success": 0,
                "error": f"Internal error: {str(e)}"
            }


# 创建服务实例
shopify_product_import_service = ShopifyProductImportService()

